﻿using Dapper;
using SetSailModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace SetSailDal
{
    public class HTSHDal
    {
        /// <summary>
        /// id获取实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static QH_ZCSH GetModel(string ID)
        {
            string sql = string.Format($@"SELECT * FROM [dbo].[QH_YHGL] with (nolock) where id='{ID}'");
            using (DBHelper help = new DBHelper())
            {
                var ls = help.DbContext.Query<QH_ZCSH>(sql) as List<QH_ZCSH>;
                if (ls.Count > 0)
                {
                    return ls[0];
                }
                else
                {
                    return new QH_ZCSH();
                }
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public static ResultModel<List<QH_ZCSH>> GetPageList(int pageIndex, int pageSize, string where, string sort = "id")
        {
            ResultModel<List<QH_ZCSH>> result = new ResultModel<List<QH_ZCSH>>();
            try
            {
                int skip = 1;//从第几条开始
                int take = pageIndex * pageSize;//到第几条结束
                if (pageIndex > 0)
                {
                    skip = ((pageIndex - 1) * pageSize) + 1;
                }
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                string sql = string.Format($@"SELECT * FROM
                            (SELECT ROW_NUMBER() OVER(ORDER BY {sort}) AS RowNum,*,'删除' as btn,'通过' as SHTG,'不通过' as SHBTG   FROM  [dbo].[QH_ZCSH] with (nolock) WHERE {where}) AS result
                            WHERE  RowNum >= {skip}   AND RowNum <= {take} ORDER BY {sort}");
                string sql_count = $"SELECT COUNT(1) FROM [dbo].[QH_ZCSH] with (nolock) where {where}";
                using (DBHelper help = new DBHelper())
                {
                    result.Data = help.DbContext.Query<QH_ZCSH>(sql) as List<QH_ZCSH>;
                    result.Count = (int)help.DbContext.ExecuteScalar(sql_count);
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        public static int GetCount(string where)
        {
            try
            {
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                string sql = string.Format(@"SELECT count(1) count FROM [dbo].[QH_ZCSH] with (nolock) WHERE {0}", where);
                using (DBHelper help = new DBHelper())
                {
                    return (int)help.DbContext.ExecuteScalar(sql);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            return 0;
        }

        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="id"></param>
        /// <param name="SHZT">审核状态：通过，未通过</param>
        /// <param name="SHZT">审核账号</param>
        /// <returns></returns>
        public static ResultModel<int> Shenhe(string ID, string SHZT)
        {
            ResultModel<int> result = new ResultModel<int>();

            string sql = string.Format(@"UPDATE [dbo].[QH_ZCSH] SET SHZT='{0}',SHZH='{1}',SHSJ=GETDATE() WHERE ID = '{2}'", SHZT, CookieData.getInstance().YHXX.DLZH, ID);

            using (DBHelper help = new DBHelper())
            {
                IDbTransaction tran = help.DbContext.BeginTransaction();
                try
                {
                    int nums = 0;
                    nums = help.DbContext.Execute(sql, "", tran);
                    if (SHZT == "通过")
                    {
                        string sqlinsert = string.Format(@"INSERT INTO [dbo].[QH_YHXX]([ID], [DLZH], [DLMM], [ZHLX], [QQ], [YY], [DDLX], [ZBFS], [ZBFJH], [CJSJ], [GXSJ], [SCBJ],[DLZT]) 
                    SELECT REPLACE(NEWID(),'-',''),ZCZH,ZCMM,'user','','',DDLX,'','',GETDATE(),GETDATE(),0,'正常' FROM [dbo].[QH_ZCSH] WHERE ID='{0}'", ID);
                        nums += help.DbContext.Execute(sqlinsert, "", tran);

                    }
                    if ((SHZT == "不通过" && nums < 1) || (SHZT == "通过" && nums < 2))
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "审核失败";
                        tran.Rollback();
                    }
                    else
                    {
                        tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    result.Code = ResultCode.Fail;
                    result.Message = ex.Message;
                    tran.Rollback();
                }
            }
            return result;
        }
    }
}
